Formula help to add on existing one i have now

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
=IFERROR(VLOOKUP($A$4:$A$208,'ConcretePrices'!$A$1:$B$505,2,0),"")

Hi I am using this formula. I have this in column D on sheetBase EST. Which pulls the value from Concrete Prices tab in column B 125.00 because Column A matches 1,000.
But what I am trying to add on this formula or make a newformula is that I need to pull in the right values by name also. If column B first name is Concrete and column A also matches the other sheet then pull the value from Concrete Prices Tab Column B. Then if Column B has the first name Grout in it and Column A matches the other sheet but now its columns D and E. Then use that value.
Maybe I can match the first name from Base EST to othersheet’s B1 and E1 first name to make it work. Not sure how to go about this.


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td]Item No.[/td][td]Work[/td][td][/td][td]Material[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td]1,000[/td][td]Concrete[/td][td][/td][td=bgcolor:#FFFF00]
$ 125.00​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td]2,000[/td][td]Concrete Waste[/td][td][/td][td]
$ 135.00​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]4,000[/td][td]Grout Waste[/td][td][/td][td]
$ 125.00​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td]5,000[/td][td]Grout Primary's[/td][td][/td][td]
$ 150.00​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Base EST[/td][/tr][/table]


second tab

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td=bgcolor:#BFBFBF]
PSI #​
[/td][td=bgcolor:#BFBFBF]
Concrete Pricing​
[/td][td=bgcolor:#000000]
​
[/td][td=bgcolor:#BFBFBF]
PSI #​
[/td][td=bgcolor:#BFBFBF]
Grout Pricing​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td]
1,000​
[/td][td]
$ 125.00 ​
[/td][td=bgcolor:#000000][/td][td]
1,000​
[/td][td]
$ 115.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td]
2,000​
[/td][td]
$ 135.00 ​
[/td][td=bgcolor:#000000][/td][td]
2,000​
[/td][td]
$ 125.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td]
3,000​
[/td][td]
$ 115.00 ​
[/td][td=bgcolor:#000000][/td][td]
3,000​
[/td][td]
$ 135.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td]
4,000​
[/td][td]
$ 125.00 ​
[/td][td=bgcolor:#000000][/td][td]
4,000​
[/td][td]
$ 125.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]
5,000​
[/td][td]
$ 150.00 ​
[/td][td=bgcolor:#000000][/td][td]
5,000​
[/td][td]
$ 145.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td]
6,000​
[/td][td]
$ 62.00 ​
[/td][td=bgcolor:#000000][/td][td]
6,000​
[/td][td]
$ 155.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td]
7,000​
[/td][td]
$ 65.00 ​
[/td][td=bgcolor:#000000][/td][td]
7,000​
[/td][td]
$ 165.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td]
8,000​
[/td][td]
$ 35.00 ​
[/td][td=bgcolor:#000000][/td][td]
8,000​
[/td][td]
$ 153.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td]
9,000​
[/td][td]
$ 45.00 ​
[/td][td=bgcolor:#000000][/td][td]
9,000​
[/td][td]
$ 145.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td]
10,000​
[/td][td]
$ 55.00 ​
[/td][td=bgcolor:#000000][/td][td]
10,000​
[/td][td]
$ 135.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]
12,000​
[/td][td]
$ 65.00 ​
[/td][td=bgcolor:#000000][/td][td]
12,000​
[/td][td]
$ 125.00 ​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td]
14,000​
[/td][td]
$ 23.00 ​
[/td][td=bgcolor:#000000][/td][td]
14,000​
[/td][td]
$ 115.00 ​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Concrete Prices[/td][/tr][/table]










[TABLE="class: head"]
<tbody>[TR]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD]

​
[/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #000000"][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[TD="bgcolor: #BFBFBF"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #000000"][/TD]
[TD][/TD]
[TD]
​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
​Was just thinking anyway to use Left,5 or something to use the lookup so it takes the right values from the right columns
 
Upvote 0
That was my thought, but if you only have Concrete and Grout, then you really only need Left,1 and if it is a C, then do your lookup in column-A on your 'Concrete Prices' sheet, otherwise do the lookup in column-D.
 
Upvote 0
Here is what I came up with, but no error checking.

=IF(LEFT(B4,1)="C",VLOOKUP(A4,Concrete Prices!A:B,2,0),VLOOKUP(A4,Concrete Prices!D:E,2,0))

Put in D4 on the 'Base EST' sheet and copy down.
 
Last edited:
Upvote 0
Wow I just edit this post. This is working thank you so much. It is possible to add the iF Error so if I run into a zero I don't get error?
 
Last edited:
Upvote 0
Hi so this is what I am using. Is there anyway to add IFERROR so just in case I don't have a number in column B that I don't get #N/A ?

=IF(LEFT(C4:C3500,1)="C",VLOOKUP(B4:B3500,'Concrete Prices'!A:B,2,0),VLOOKUP(B4:B3500,'Concrete Prices'!D:E,2,0))
 
Upvote 0
I'm sure there are better ways, but here is how I would do it based on my other formula and based on the rows and information you posted in your 1st post:

=IF(A4="","",IF(LEFT(B4,1)="C",VLOOKUP(A4,Sheet2!A:B,2,0),VLOOKUP(A4,Sheet2!D:E,2,0)))

Put that in D4 and copy down.
 
Upvote 0
ok thanks for the help my end results =IF(B4:B3500="","",IF(LEFT(B4:B3500,1)="C",VLOOKUP(B4:B3500, 'Concrete Prices'!A:B,2,0),VLOOKUP(B4:B3500,'Concrete Prices'!D:E,2,0)))
 
Upvote 0
Based on your 1st post and data I wouldn't think that would work as your vlookup's don't appear to be correct to me, but if it is working and you are getting the expected results that is fine. To me you would always want the lookup value to be A4:A3500, and I really don't think you need to range it, if you copy it down it will put in the proper cell reference and work.

But again, if it is working your actual data may be different than what you posted.
 
Upvote 0
Hi I can't because its being used in different cells going down. I wish I can put it in cell 4 and drag it down to 3000 cells, but I can't. I am having a small issue with this new formula so that it doesn't give me back error. Trying to figure out why
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Add item in first digits with create space after it based on sheet name
Replies
8
Views
111
kvsrinivasamurthy
K
E
  • Question Question
Write formula to compare planned amounts by vendor in one sheet to approved amounts by vendor in another tab based on project ID
Replies
1
Views
548
OaklandJim
O
M
  • Question Question
Index Match function List based on date
Replies
7
Views
271
Fluff
Fluff
F
  • Solved
Macro or formula to populate a row in one sheet only if data entered in another is non-zero
Replies
3
Views
836
FlowersinExcel
F
A
  • Question Question
Help with Array Formulae
Replies
0
Views
499
AB1984
A

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top