Formula help to add on existing one i have now

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,129
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:
Sort by date Sort by votes

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
no its fine the way it is. The last one you helped me with wasn't working right but I think I got it to work now.
 
Upvote 0
one last thing so this is whats working for me. Yes this is a different range but its working.

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

The issue I am having is if the cells in column B are empty then ill get a return #N/A. I am trying to avoid return this if that's blank. I want it ot return nothing
 
Upvote 0
zone709 said:
one last thing so this is whats working for me. Yes this is a different range but its working.

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

The issue I am having is if the cells in column B are empty then ill get a return #N/A. I am trying to avoid return this if that's blank. I want it ot return nothing
Click to expand...

Try this:
=IF(isblank(c4),"",if(LEFT(C4:C5000,1)="C",VLOOKUP(B4:B5000,'Concrete Prices'!A:B,2,0),VLOOKUP(B4:B5000,'Concrete Prices'!D:E,2,0)))
 
Upvote 0
Thanks this did it. I think I'm good now :)

=IF(ISBLANK(B4:B3500),"",IF(LEFT(C4:C5000,1)="C",VLOOKUP(B4:B5000,'Concrete Prices'!A:B,2,0),VLOOKUP(B4:B5000,'Concrete Prices'!D:E,2,0)))
 
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
130
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
555
OaklandJim
O
M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
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
842
FlowersinExcel
F
A
  • Question Question
Help with Array Formulae
Replies
0
Views
503
AB1984
A

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

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